<?php
// +----------------------------------------------------------------------
// | 数据库导出类 (for thinkphp)
// +----------------------------------------------------------------------
// | @link ( http://www.yurnero.net )
// +----------------------------------------------------------------------
// | @copyright
// +----------------------------------------------------------------------
// | @licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | @author Haijun Wu <nicholasinlove@126.com>
// +----------------------------------------------------------------------
// | $Id: Sql.class.php 120 2011-05-16 09:13:58Z nicholasinlove1986@gmail.com $
// +----------------------------------------------------------------------

/**
 +------------------------------------------------------------------------------
 * 使用实例
 +------------------------------------------------------------------------------
 * import("@.ORG.Sql");			                               导入库
 * $dump = new Sql ();                                         实例化
 * $tables = $dump->listTables();		                       获取表集合		
 * $dump->dumpTable($path,$file_name,$tables,$max_size);       备份sql
 * $dump->sqlImport($path);                                    导入sql
 * $dump->getHead($path);                                      获取备份文件信息
 +------------------------------------------------------------------------------
 */
 
class Sql extends Think {

    public $db;
	public $dump_sql         =  '';        //以字符串返回表定义和数据定义
	public $dump_sql_array   =  array();   //以一维数组返回表定义和数据定义
	public $vol              =  1;         // 文件卷数   
	public $max_size         =  2097152;   // 2m

	public function __construct() {
		$this->db = Db::getInstance();
	}
	
	/**
	 *
     * 获取表集合，以一维数组的形式返回
     *
     * @access  public
     *
     * @return array
     */
	public function listTables() {
		$sql = 'SHOW TABLES FROM '.C('DB_NAME');
		$result = $this->db->query($sql);
		$info   =   array();
		foreach ($result as $val) {
			if(substr(current($val),0,strlen(C('DB_PREFIX'))) == C('DB_PREFIX')) { 
				$info[] = current($val);
			}	
		}
		return $info;
	}
	
	/**
     * 获取指定表的定义,数据定义 以字符串或一维数组的形式返回
     * 表定义 ,数据定义大于指定的字节数则以一维数组的形式返回
	 * 
     * @access public
     *
     * @param array    $tables     数据库表集合
     * @param integer  $max_size   字节数(默认2097152 = 2m)
     *
     * @return string or array
     */
	public function getTableDfData($tables,$max_size) {
		if ($max_size > 0 ) {
			$this->max_size = $max_size;
		}
		$j = 1;
		//备份文件头部
		$this->dump_sql .= $this->makeHead($this->vol);
		foreach ($tables as $table) {	
		
			//获取表结构
			$tmp_arr = $this->db->query("SHOW CREATE TABLE `$table`");
			$tmp_sql = $tmp_arr[0]['Create Table'];
			$this->dump_sql .= "DROP TABLE IF EXISTS `$table`;\r\n".$tmp_sql.";\r\n";
			
			//获取表字段总数
			$data = $this->db->query("select * from $table");		
			$data_count = count($data);
            $fields = array_keys($data[0]);		
			
			//构造INSERT语句
			for ($i=0; $i<$data_count; $i++) {
				$comma = '';
				$this->dump_sql .= "INSERT INTO `$table` ( `" . implode("`, `", $fields) . "` ) VALUES (";
				$data_count_ = count($data[$i]);
				for ($x=0; $x< $data_count_; $x++) {
					$this->dump_sql .= $comma."'".$this->escape_string($data[$i][$fields[$x]])."'";
					$comma = ",";
				}
				$this->dump_sql .= ");\r\n";
				if (strlen($this->dump_sql) > $this->max_size) {
					$this->dump_sql .= "\r\n";
					$this->dump_sql .= "-- end nblog ".VERSION." sql dump program";
					$this->dump_sql_array[] = $this->dump_sql; 
					$this->dump_sql = '';
					$this->dump_sql .= $this->makeHead($this->vol+$j);
					$j++;
				}			
			}
			$this->dump_sql .= "\r\n";
		}
		$this->dump_sql .= "-- end nblog ".VERSION." sql dump program";
		if (is_array($this->dump_sql_array) && count($this->dump_sql_array) > 0) {
			array_push($this->dump_sql_array,$this->dump_sql);
			return $this->dump_sql_array;
		} else {
			return $this->dump_sql;
		}
	}
	 
	/**
     *  备份数据
     *  如果备份数据超过默认的大小，程序会自动采用分卷备份功能
	 *
     * @access  public
	 * @param   string      $path       保存路径表名的文件
	 * @param   array       $tables     数据库表集
     * @param   string      $file_name  文件名
	 * @param   int         $max_size   字节数
     *
     * @return bool
     */
	public function dumpTable($path,$file_name,$tables,$max_size) {
		//开始备份
		$datas  =  $this->getTableDfData($tables,$max_size);		
			
		//将备份信息写入文件
		if (is_array($datas)) {
			$this->vol = count($datas);
			$i = 1;
			foreach ($datas as $val) {
				$filepath = $path."/".$file_name."_{$i}.sql";
				if (!$handle = @fopen($filepath,"w+")) {
					$this->ErrorHandle("cannot read");
					return false;
				}
				if (!@fwrite($handle,$val)) {
					$this->ErrorHandle("cannot write");
					fclose($handle); unlink($filepath);
					return false;
				}
				$i++;
			}
		}else {
			$this->vol = 1;
			$filepath = $path."/".$file_name.".sql";
			if (!$handle = @fopen($filepath,"w+")) {
				$this->ErrorHandle("cannot read");
			}
			if (!@fwrite($handle,$datas)) {
				$this->ErrorHandle("cannot write");
				fclose($handle); unlink($filepath);
				return false;
			}
		}
		fclose($handle);
		return true;
	}
	
	/**
	 * 导入sql
	 *
	 * @access  public
	 * @param   string  $path  保存路径表名的文件
	 *
	 * @return void
	 */
	public function sqlImport($path) {
		//过滤
		$sql_str = array_filter(file($path), 'remove_comment');
		$sql_str = str_replace("\r", '', implode('', $sql_str));
	
		$ret = explode(";\n", $sql_str);
		$ret_count = count($ret);
	
		//执行sql语句
		for ($i = 0; $i < $ret_count; $i++) {
			$ret[$i] = trim($ret[$i], " \r\n;"); //剔除多余信息
			if (!empty($ret[$i])) {
				$this->db->query($ret[$i]);
			}
		}
		return true;
	}
	
	/**
     * 生成备份文件头部
     *
     * @access  public
     *
     * @return  string  $str    备份文件头部
     */
    public function makeHead($vol) {
        /* 系统信息 */
        $sys_info['os']         = PHP_OS;
        $sys_info['php_ver']    = PHP_VERSION;
        $sys_info['mysql_ver']  = $this->version();
        $sys_info['date']       = date('Y-m-d H:i:s');

        $head =  "-- nblog ".VERSION." sql dump program\r\n".
                 "-- http://www.yurnero.net\r\n".
                 "-- \r\n".
                 "-- date : ".$sys_info["date"]."\r\n".
                 "-- mysql server version : ".$sys_info['mysql_ver']."\r\n".
                 "-- php version : ".$sys_info['php_ver']."\r\n".
                 "-- nblog version : ".VERSION."\r\n".
				 "-- vol : " . $vol . "\r\n".
				 "\r\n";
				 
        return $head;
    }


	/**
     *  获取备份文件信息
     *
     * @access  public
     * @param   string      $path       保存路径表名的文件
     *
     * @return  array       $arr        信息数组
     */
    static function getHead($path) {
        /* 获取sql文件头部信息 */
        $sql_info = array('date'=>'', 'mysql_ver'=> '', 'php_ver'=>0, 'nblog_ver'=>'', 'vol'=>0);
        $fp = fopen($path,'rb');
        $str = fread($fp, 250);
        fclose($fp);
        $arr = explode("\n", $str);

        foreach ($arr as $val) {
            $pos = strpos($val, ':');
            if ($pos > 0) {
                $type = trim(substr($val, 0, $pos), "-\n\r\t ");
                $value = trim(substr($val, $pos+1), "/\n\r\t ");
                if ($type == 'date') {
                    $sql_info['date'] = $value;
                } elseif ($type == 'mysql server version') {
                    $sql_info['mysql_ver'] = $value;
                } elseif ($type == 'php version') {
                    $sql_info['php_ver'] = $value;
                } elseif ($type == 'nblog version') {
                    $sql_info['nblog_ver'] = $value;
                } elseif ($type == 'vol') {
                    $sql_info['vol'] = $value;
                }
            }
        }
        return $sql_info;
    }
	
	/**
	 * 对mysql敏感字符串转义
	 *
	 * @access  public
	 * @param   string      $str
	 *
	 * @return  string
	 */
	public function escape_string($unescaped_string) {
        if (PHP_VERSION >= '4.3') {
            return mysql_real_escape_string($unescaped_string);
        } else {
            return mysql_escape_string($unescaped_string);
        }
    }
	
	/**
	 * 对mysql记录中的null值进行处理
	 *
	 * @access  public
	 * @param   string      $str
	 *
	 * @return  string
	 */
	public function dump_null_string($str) {
		if (!isset($str) || is_null($str)) {
			$str = 'NULL';
		}	
		return $str;
	}
	
	/**
	 * 获取mysql版本
	 *
	 * @access  public
	 *
	 * @return  string
	 */
	public function version() {
		//return mysql_get_server_info($this->link);
		$db_ver = $this->db->query('select version();');
		$db_ver = $db_ver[0]['version()'];
		return $db_ver;
	}
	
	/**
	 * 错误消息
	 *
	 * @access  public
	 * @param   string      $str
	 *
	 * @return  string
	 */
	private function ErrorHandle($msg) {
		throw new Exception($msg);
		//echo $msg;
		//return;		
	}
	
	/**
     *  返回一个随机的名字
     *
     * @access  public
     * @param
     *
     * @return      string      $str    随机名称
     */
    public function get_random_name() {
        $str = date('Ymd');
        for ($i = 0; $i < 6; $i++) {
            $str .= chr(mt_rand(97, 122));
        }
        return $str;
    }
		
}

?>